<?php

namespace app\index\controller;
use think\Db as warehouseDB;

class FusionDataWarehouse extends Common{

    /**
     * 查询师资队伍
     */
    public function getTeachersTeam(){
        try {
            //头衔/人数
            //$sql = "SELECT title,COUNT(title) count FROM scholar GROUP BY title";
            $sql = "SELECT job_title title,COUNT(job_title) count FROM user "
                    ."WHERE job_title is not null and job_title != '' "
                    ."GROUP BY job_title";
            $titleResult = warehouseDB::query($sql);

            //年度人才数
            $sql = "SELECT year,type,COUNT(type) typeCount FROM scholar_talents GROUP BY year,type ORDER BY year";
            $talentsResult = warehouseDB::query($sql);

            //性别分布
            $sql = "SELECT sex,COUNT(sex) count FROM scholar GROUP BY sex";
            $sexResult = warehouseDB::query($sql);

            //年龄分布
            $sql = "select ageScope,count(ageScope) ageCount from ( "
                    ."SELECT CASE "
                    ."when age<35 then '<35' "
                    ."when age>=35 and age<45 then '35~45' "
                    ."when age>=45 and age<55 then '45~55' "
                    ."when age>=55 and age<65 then '55~65' "
                    ."when age>=65 then '>65' "
                    ."END "
                    ."as ageScope from scholar "
                    .")a GROUP BY ageScope";
            $ageResult = warehouseDB::query($sql);

            //籍贯分布
            $sql = "SELECT hometown,COUNT(hometown) hometownCount FROM scholar GROUP BY hometown";
            $hometownResult = warehouseDB::query($sql);

            //学位分布
            $sql = "SELECT academic_degree academicDegree,COUNT(academic_degree) academicDegreeCount FROM scholar "
                    ." WHERE academic_degree <> '' and academic_degree is not null GROUP BY academic_degree ORDER BY academicDegreeCount desc";
            $degreeResult = warehouseDB::query($sql);

            //学科门类分布
            //$sql = "SELECT subject,COUNT(subject) subjectCount FROM scholar GROUP BY subject";
            /*$sql = "SELECT subject,COUNT(subject) subjectCount FROM scholar "
                    ." WHERE subject <> '' and subject is not null GROUP BY subject ORDER BY subjectCount desc";*/
            $sql = "SELECT subject,count subjectCount FROM scholar_subjects_count "
                    ." WHERE subject <> '' and subject is not null  ORDER BY count desc";
            $subjectResult = warehouseDB::query($sql);

            //职称分布
            $sql = "SELECT professional_title professionalTitle,COUNT(professional_title) professionalTitleCount FROM scholar "
                    ." WHERE professional_title <> '' and professional_title is not null GROUP BY professional_title ORDER BY professionalTitleCount desc ";
            $professionalTitleResult = warehouseDB::query($sql);

            $returnData = [];
            $returnData['title'] = $titleResult;
            $returnData['talents'] = $talentsResult;
            $returnData['sex'] = $sexResult;
            $returnData['age'] = $ageResult;
            $returnData['hometown'] = $hometownResult;
            $returnData['degree'] = $degreeResult;
            $returnData['subject'] = $subjectResult;
            $returnData['professionalTitle'] = $professionalTitleResult;

            if(empty($returnData)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $returnData);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 合作分布
     */
    public function getCooperateDistributed(){
        try {
            $sql = "SELECT area,SUM(agency_count) agencySum FROM scholar_cooperate_distributed GROUP BY area";
            $result = warehouseDB::query($sql);

            if(empty($result)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $result);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 科研项目与奖励
     */
    public function getResearchAward(){
        $this->datas = $this->params;
        $condition = " WHERE a.projectOriginCount >= 50 ";
        try {
            if(array_key_exists('count',$this->datas)){
                $condition = " WHERE a.projectOriginCount >= ".$this->datas['count'];
            }
            /*$sql = "SELECT year,projectOrigin,COUNT(projectOrigin) projectOriginCount FROM resource_collection "
                    ."WHERE resourceCode = 'O' and YEAR is not null and projectOrigin != '' GROUP BY year,projectOrigin";*/
            $sql = "SELECT a.* FROM (SELECT year,projectOrigin,COUNT(projectOrigin) projectOriginCount FROM resource_collection "
                ."WHERE resourceCode = 'O' and YEAR is not null and projectOrigin != '' "
                ."GROUP BY year,projectOrigin ORDER BY projectOrigin,YEAR ) a ".$condition;
            $result = warehouseDB::query($sql);

            if(empty($result)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $result);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 获取科研成果和历年成果分布
     */
    public function getScientificResearchResults(){
        try {
            //首页-科研成果
            //成果数量
            $yearEnd = date("Y");
            $yearBegin = $yearEnd - 2;
            $sql = "select a.*,b.resultSum from (SELECT resourceCode,COUNT(resourceCode) count FROM resource_collection "
                    ." where year = ".$yearEnd." GROUP BY resourceCode) a "
                    ." left join (SELECT resourceCode,COUNT(1) resultSum FROM resource_collection "
                    ." where  year >= ".$yearBegin." and year <= ".$yearEnd." GROUP BY resourceCode) b "
                    ." on a.resourceCode = b.resourceCode ";
            $resultStandard = warehouseDB::query($sql);
            /*$sql = "SELECT COUNT(1) resultSum FROM resource_collection where  year >= ".$yearBegin." and year <= ".$yearEnd
                    ." GROUP BY resourceCode";
            $resultSum = warehouseDB::query($sql);

            $sql = "SELECT resourceCode,COUNT(resourceCode) count FROM resource_collection where year = ".$yearEnd
                ." GROUP BY resourceCode";
            $resultOther = warehouseDB::query($sql);
            $resultStandard = array_merge($resultSum,$resultOther);*/

            //级别数量和转让次数
            $sql = "SELECT resourceCode,level,count(level) times FROM resource_collection "
                ."WHERE resourceCode != 'P' GROUP BY resourceCode,level ORDER BY resourceCode";
            $levelResult = warehouseDB::query($sql);

            $sql = "SELECT resourceCode,sum(transferCount) transferCount FROM resource_collection "
                ."WHERE resourceCode = 'P' GROUP BY resourceCode";
            $transferResult = warehouseDB::query($sql);
            $levelTralsferResult = array_merge($levelResult,$transferResult);

            //首页-历年成果分布
            $sql = "SELECT year,COUNT(resourceCode) amount,resourceCode FROM resource_collection "
                ."GROUP BY year,resourceCode ORDER BY resourceCode";
            $resuleYearAmount = warehouseDB::query($sql);


            $returnArray = [];
            $returnArray['resultCount'] = $resultStandard;
            $returnArray['levelCount'] = $levelTralsferResult;
            $returnArray['academicLifeCycle'] = $resuleYearAmount;


            if(empty($resultStandard) && empty($levelTralsferResult) && empty($academicLifeCycleResult)){
                $this->returnMsg(1, '暂无数据！');
            }
            $this->returnMsg(1, '获取数据成功！', $returnArray);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }
}